

# Create your views here.
import json
import pickle

from django.http import HttpResponse, HttpResponseBadRequest

from common.constant import department_person_file, HalfDay
from common.sql_constant_enum import case_type_constant_responding_Chinese, case_type_constant_list, \
    case_status_constant_responding_English, case_execute_result_constant_responding_English, \
    case_type_constant_responding_English, case_status_constant_responding_Chinese, \
    case_execute_result_constant_responding_Chinese, case_execute_result_constant_list, case_status_constant_list
from common.sql_instance_config import department_person_info_list_sql
from common.sql_instance_execute import case_all_project_name_list_sql, construct_sql_execute_times_statistics_all, \
    construct_sql_execute_case_statistics_all, construct_sql_case_test_sheet_by_project_search_sql, \
    construct_sql_case_subsys_by_project_search_sql
from common.sql_instance_testcase import *
from utils.cursorHandler import cursor, CursorHandler
from utils.normal_simple_function import fetch_file_abs_path, serialize_json_dumps, serialize_json_date_type, \
    redis_data_get


async def personProjectSubsysCaseExecuteTotal(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        print('-----------', params)
        # 项目ids
        project_id = params.get('project_id', None)
        # case创建人
        executor = params.get('tester', None)
        # case创建起始时间
        date_execute_time_start = params.get('date_execute_time_start', None)
        # case创建终止时间
        date_execute_time_end = params.get('date_execute_time_end', None)
        # 严重等级
        case_type = params.get('case_type', None)
        # 页码
        pageNum = params.get('pageNum', 0)
        # 页显示数
        pageSize = params.get('pageSize', 25)


        # 按照数据类型分类
        case_execute_statistics_sql = construct_person_system_subsys_case_execute_statistics(
            _testers=tuple(executor),
            _project_ids=tuple(project_id),
            _date_start_time=date_execute_time_start,
            _date_end_time=date_execute_time_end,
            _case_type=tuple(case_type_constant_responding_Chinese.get(each_type) for each_type in case_type),
            _page_No=pageNum,
            _page_size=pageSize)
        print(case_execute_statistics_sql)
        person_case_execute_search_result = cursor.search_alone(case_execute_statistics_sql)
        # print('person_case_execute_search_result', person_case_execute_search_result[:2])

        # 用于结果挂载
        person_case_list = []
        # 人员项目数据集合
        person_data_dict = {}
        # 人员判断是否已经聚合过
        each_tester_name_set = set()
        # 项目id判断
        project_ids_tmp = set()
        # 统计每个人的所有的case
        person_total_case = 0
        # 是否加人员总数
        flag_person_num_add = False

        # 过滤非测试人员
        tester_standard_json = redis_data_get('department_person_file', search_sql=department_person_info_list_sql, data_timeout=HalfDay)
        tester_standard_set = {each_person['tester_name'] for each_person in tester_standard_json}
        # 遍历返回的所有的测试人员与子系统case数
        for each_person_subsys in person_case_execute_search_result:
            tester = each_person_subsys['tester']
            project_name = each_person_subsys['project_name']
            project_id = each_person_subsys['project_id']
            subsys_id = each_person_subsys['subsys_id']
            subsys_name = each_person_subsys['subsys_name']
            total = each_person_subsys['total']
            # 过滤掉非测试人员
            if tester not in tester_standard_set:
                continue
            if tester not in each_tester_name_set:
                # 新的测试人员数据，则初始化项目容器
                project_ids_tmp = set()
                # 判断是否已有上个数据结果
                if person_data_dict:
                    person_data_dict['person_total_case'] = sum([each_project['case_num'] for each_project in person_data_dict['project_case_list']])
                    person_case_list.append(person_data_dict)
                    # 清空个人数据结果
                    person_data_dict = {}
                    person_total_case = 0
                person_data_dict['person_name'] = tester
                person_data_dict['project_case_list'] = []
                # 加入到项目id池中
                project_ids_tmp.add(project_id)
                # 初始化case总数
                project_case_nums = 0
                # 初始化返回数据结构
                person_data_dict['project_case_list'].append(
                    {
                        "project_id": project_id,
                        "project_name": project_name,
                        "case_num": project_case_nums,
                        "subsys_case_list": [
                            {
                                "subsys_id": subsys_id,
                                "subsys_name": subsys_name,
                                "case_num": total,
                                "case_types": [
                                    {
                                        "type": each_type,
                                        "nums": each_person_subsys.get(each_type)
                                    } for each_type in case_type_constant_responding_Chinese.values()
                                ]
                            }
                        ]
                    }
                )
                # 此测试人员的信息做更新
                each_tester_name_set.add(tester)
            # 处理项目与子系统的数据
            else:
                flag_person_num_add = True
                # 没有统计过的项目，统计项目与子系统
                if project_id not in project_ids_tmp:
                    project_ids_tmp.add(project_id)
                    # 初始化case总数
                    project_case_nums = 0
                    person_data_dict['project_case_list'].append(
                        {
                            "project_id": project_id,
                            "project_name": project_name,
                            "case_num": project_case_nums,
                            "subsys_case_list": [
                                {
                                    "subsys_id": subsys_id,
                                    "subsys_name": subsys_name,
                                    "case_num": total,
                                    "case_types": [
                                        {
                                            "type": each_type,
                                            "nums": each_person_subsys.get(each_type)
                                        } for each_type in case_type_constant_responding_Chinese.values()
                                    ]
                                }
                            ]
                        }
                    )

                # 已统计过的项目，统计子系统
                else:
                    person_data_dict['project_case_list'][-1]['subsys_case_list'].append(
                        {
                            "subsys_id": subsys_id,
                            "subsys_name": subsys_name,
                            "case_num": total,
                            "case_types": [
                                {
                                    "type": each_type,
                                    "nums": each_person_subsys.get(each_type)
                                } for each_type in case_type_constant_responding_Chinese.values()
                            ]
                        }
                    )
            # 更新项目case数
            project_case_nums += total
            person_data_dict['project_case_list'][-1]['case_num'] = project_case_nums
        else:
            # 计算最后一个人员的用例总数
            person_data_dict['person_total_case'] = sum(
                [each_project['case_num'] for each_project in person_data_dict['project_case_list']] if person_data_dict else [0]
            )
            person_case_list.append(person_data_dict)
        # 组织结果数据
        # 返回数据结构构造
        json_returns = {'data': {'person_case_list': person_case_list}}
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def caseTypeList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        print(case_type_constant_list)

        json_returns = {
            'data': {
                "type_names": case_type_constant_list,
                'total': len(case_type_constant_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')



async def projectList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # subsys_id = int(request.GET.get('subsys_id', 0))

        print(f'【Bug】version_sql: {case_all_project_name_list_sql}')
        # 查询测试人员名单
        project_list = cursor.search_alone(case_all_project_name_list_sql)
        # 这里是为了查询没有关联项目的人员
        project_list.insert(0,
            {'id': -1, 'name': ' ', 'status': ' '}
        )
        # print(f'【execute】project 列表的查询结果: {project_list}')
        json_returns = {
            'data': {
                "project_names": project_list,
                'total': len(project_list)
            }
        }
        response = HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def caseExecuteTotal(request):
    return HttpResponse()


async def personProjectSubsysCaseCreateTotal(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        print('-----------', params)
        # 项目ids
        project_id = params.get('project_id', None)
        # case创建人
        creator = params.get('creator', None)
        # case创建起始时间
        date_create_time_start = params.get('date_create_time_start', None)
        # case创建终止时间
        date_create_time_end = params.get('date_create_time_end', None)
        # 严重等级
        case_type = params.get('case_type', None)
        # 页码
        pageNum = params.get('pageNum', 0)
        # 页显示数
        pageSize = params.get('pageSize', 25)

        # 按照数据类型分类
        case_execute_statistics_sql = construct_person_system_subsys_case_create_statistics(
            _testers=tuple(creator),
            _project_ids=tuple(project_id),
            _date_start_time=date_create_time_start,
            _date_end_time=date_create_time_end,
            _case_type=tuple(case_type_constant_responding_Chinese.get(each_type) for each_type in case_type),
            _page_No=pageNum,
            _page_size=pageSize)
        print(case_execute_statistics_sql)
        person_case_execute_search_result = cursor.search_alone(case_execute_statistics_sql)
        print('person_case_execute_search_result', person_case_execute_search_result[:2])

        # 用于结果挂载
        person_case_list = []
        # 人员项目数据集合
        person_data_dict = {}
        # 人员判断是否已经聚合过
        each_tester_name_set = set()
        # 项目id判断
        project_ids_tmp = set()
        # 统计每个人的所有的case
        person_total_case = 0

        # 过滤非测试人员
        tester_standard_json = redis_data_get('department_person_file', search_sql=department_person_info_list_sql, data_timeout=HalfDay)
        tester_standard_set = {each_person['tester_name'] for each_person in tester_standard_json}
        # 遍历返回的所有的测试人员与子系统case数
        for each_person_subsys in person_case_execute_search_result:
            tester = each_person_subsys['tester']
            project_name = each_person_subsys['project_name']
            project_id = each_person_subsys['project_id']
            subsys_id = each_person_subsys['subsys_id']
            subsys_name = each_person_subsys['subsys_name']
            total = each_person_subsys['total']
            # 过滤掉非测试人员
            if tester not in tester_standard_set:
                continue
            if tester not in each_tester_name_set:
                # 新的测试人员数据，则初始化项目容器
                project_ids_tmp = set()
                # 判断是否已有上个数据结果
                if person_data_dict:
                    person_data_dict['person_total_case'] = person_total_case
                    person_case_list.append(person_data_dict)
                    # 清空个人数据结果
                    person_data_dict = {}
                    person_total_case = 0
                person_data_dict['person_name'] = tester
                person_data_dict['project_case_list'] = []
                # 加入到项目id池中
                project_ids_tmp.add(project_id)
                # 初始化case总数
                project_case_nums = 0
                # 初始化返回数据结构
                person_data_dict['project_case_list'].append(
                    {
                        "project_id": project_id,
                        "project_name": project_name,
                        "case_num": project_case_nums,
                        "subsys_case_list": [
                            {
                                "subsys_id": subsys_id,
                                "subsys_name": subsys_name,
                                "case_num": total,
                                "case_types": [
                                    {
                                        "type": each_type,
                                        "nums": each_person_subsys.get(each_type)
                                    } for each_type in case_type_constant_responding_Chinese.values()
                                ]
                            }
                        ]
                    }
                )
                # 此测试人员的信息做更新
                each_tester_name_set.add(tester)
            # 处理项目与子系统的数据
            else:
                # 没有统计过的项目，统计项目与子系统
                if project_id not in project_ids_tmp:
                    project_ids_tmp.add(project_id)
                    # 初始化case总数
                    project_case_nums = 0
                    person_data_dict['project_case_list'].append(
                        {
                            "project_id": project_id,
                            "project_name": project_name,
                            "case_num": project_case_nums,
                            "subsys_case_list": [
                                {
                                    "subsys_id": subsys_id,
                                    "subsys_name": subsys_name,
                                    "case_num": total,
                                    "case_types": [
                                        {
                                            "type": each_type,
                                            "nums": each_person_subsys.get(each_type)
                                        } for each_type in case_type_constant_responding_Chinese.values()
                                    ]
                                }
                            ]
                        }
                    )

                # 已统计过的项目，统计子系统
                else:
                    person_data_dict['project_case_list'][-1]['subsys_case_list'].append(
                        {
                            "subsys_id": subsys_id,
                            "subsys_name": subsys_name,
                            "case_num": total,
                            "case_types": [
                                {
                                    "type": each_type,
                                    "nums": each_person_subsys.get(each_type)
                                } for each_type in case_type_constant_responding_Chinese.values()
                            ]
                        }
                    )
            # 更新项目case数
            project_case_nums += total
            person_data_dict['project_case_list'][-1]['case_num'] = project_case_nums
            # 统计本人的所有case的数据结果更新
            person_total_case += project_case_nums
        else:
            person_data_dict['person_total_case'] = sum(
                [each_project['case_num'] for each_project in person_data_dict['project_case_list']]) if person_data_dict.get('project_case_list') else 0
            person_case_list.append(person_data_dict)
        # 组织结果数据
        # 返回数据结构构造
        json_returns = {'data': {'person_case_list': person_case_list}}
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executeResultList(request):
    return HttpResponse()


async def caseFromList(request):
    return HttpResponse()


async def requirementsNameList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        # 查询测试人员名单
        requirements_list = cursor.search_alone(testcase_requirement_name_list_sql)
        # print(requirements_list)
        json_returns = {
            'data': {
                "tester_names": [
                    {
                        "id": each.get("id"),
                        "name": each.get("name")
                    } for each in requirements_list
                ],
                'total': len(requirements_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def outputCaseExecute(request):
    return HttpResponse()


async def outputCaseCreate(request):
    return HttpResponse()


async def creatorList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        # 查询测试人员名单
        tester_name_list = cursor.search_alone(case_tester_name_list_sql)
        print(tester_name_list)

        json_returns = {
            'data': {
                "tester_names": tester_name_list,
                'total': len(tester_name_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executorList(request):
    return HttpResponse()


async def assignerList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        # 查询测试人员名单
        tester_name_list = cursor.search_alone(testcase_assigner_name_list_sql)
        print(tester_name_list)
        # 过滤非测试人员
        tester_standard_json = redis_data_get('department_person_file', search_sql=department_person_info_list_sql, data_timeout=HalfDay)
        tester_standard_set = {each_person['tester_name'] for each_person in tester_standard_json}
        tester_assigner_list = [tester for tester in tester_name_list if tester['name'] in tester_standard_set]
        json_returns = {
            'data': {
                "tester_names": tester_assigner_list,
                'total': len(tester_assigner_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')



async def caseIdList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        # 查询测试人员名单
        testcase_id_list = cursor.search_alone(testcase_id_list_sql)
        # print(testcase_id_list)

        json_returns = {
            'data': {
                "testcase_id": testcase_id_list,
                'total': len(testcase_id_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')



async def caseStatistics(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        print('-----------', params)
        # 项目id
        project_id = params.get('project_id', None)
        # 子系统id
        subsys_id = params.get('subsys_id', None)
        # 测试单id
        test_sheet_id = params.get('test_sheet_id', None)
        # 迭代id
        iteration_id = params.get('iteration_id', None)
        # 版本id
        version_id = params.get('version_id', None)
        # case创建人
        creator_name = params.get('creator_name', None)
        # 执行人
        executor_name = params.get('executor_name', None)
        # 指派给
        assigner_name = params.get('assigner_name', None)
        # 用例id
        case_id = params.get('case_id', None)
        # 执行结果
        execute_result = params.get('execute_result', None)
        # 执行起始日期
        date_execute_start = params.get('date_execute_start', None)
        # 执行截止日期
        date_execute_end = params.get('date_execute_end', None)
        # 创建起始日期
        date_create_start = params.get('date_create_start', None)
        # 创建截止日期
        date_create_end = params.get('date_create_end', None)
        # 用例类型
        case_type = params.get('case_type', None)
        case_type = case_type if type(case_type) is str else case_type[0]
        print('=============>', case_type)
        # 页码
        pageNo = params.get('pageNo', 0)
        # 页码显示数量
        pageNum = params.get('pageNum', 25)

        # case 条件查询的数据结果
        case_condition_search_result = []
        # 按照数据类型分类
        case_extension_sql = construct_sql_case_statistics_detail(
                                        _project_id=project_id,
                                        _subsys_id=subsys_id,
                                        _iteration_id=iteration_id,
                                        _version_id=version_id,
                                        _test_sheet_id=test_sheet_id,
                                        _case_id=case_id,
                                        _case_type=case_type_constant_responding_Chinese.get(case_type),
                                        _assigner_name=assigner_name,
                                        _executor_name=executor_name,
                                        _creator_name=creator_name,
                                        _date_create_start=date_create_start,
                                        _date_create_end=date_create_end,
                                        _execute_result=case_execute_result_constant_responding_Chinese.get(execute_result),
                                        _date_execute_start=date_execute_start,
                                        _date_execute_end=date_execute_end,
                                        _pageNum=pageNum,
                                        _pageNo=pageNo)
        case_extension_total_sql = construct_sql_case_statistics_all_sql_detail(
            _project_id=project_id,
            _subsys_id=subsys_id,
            _iteration_id=iteration_id,
            _version_id=version_id,
            _test_sheet_id=test_sheet_id,
            _case_id=case_id,
            _case_type=case_type_constant_responding_Chinese.get(case_type),
            _assigner_name=assigner_name,
            _executor_name=executor_name,
            _creator_name=creator_name,
            _date_create_start=date_create_start,
            _date_create_end=date_create_end,
            _execute_result=case_execute_result_constant_responding_Chinese.get(execute_result),
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end
        )
        print('查询数据结果', case_extension_sql)
        print('统计结果', case_extension_total_sql)
        case_condition_search_result = cursor.search_alone(case_extension_sql)
        case_total = cursor.search_alone(case_extension_total_sql)[0].get('total')
        print('输出3个数据作为样式', case_condition_search_result[:3])

        # # 判断执行人，项目，子系统，执行起始时间，执行终止时间，有一个不为空，则统计用例总数与执行总数
        # execute_case_total = -1
        # execute_times_total = -1
        # if executor_name or project_id or test_sheet_id or subsys_id or \
        #         date_execute_start or date_execute_end:
        #     # 执行用例总数
        #     case_execute_all_sql = construct_sql_execute_case_statistics_all(
        #         _project_id=project_id,
        #         _subsys_id=subsys_id,
        #         _test_sheet_id=test_sheet_id,
        #         _executor_name=executor_name,
        #         _date_execute_start=date_execute_start,
        #         _date_execute_end=date_execute_end,
        #         _case_status=case_status_constant_responding_Chinese.get(case_status),
        #         _case_type=case_type_constant_responding_Chinese.get(case_type),
        #     )
        #     print(case_execute_all_sql)
        #     execute_case_total = cursor.search_alone(case_execute_all_sql)[0].get('total')
        #     # 执行总次数
        #     execute_times_all_sql = construct_sql_execute_times_statistics_all(
        #         _project_id=project_id,
        #         _subsys_id=subsys_id,
        #         _test_sheet_id=test_sheet_id,
        #         _executor_name=executor_name,
        #         _date_execute_start=date_execute_start,
        #         _date_execute_end=date_execute_end,
        #         _case_status=case_status_constant_responding_Chinese.get(case_status),
        #         _case_type=case_type_constant_responding_Chinese.get(case_type),
        #     )
        #     print(execute_times_all_sql)
        #     execute_times_total = cursor.search_alone(execute_times_all_sql)[0].get('total')

        json_returns = {
            'data': {
                # case总数
                "case_total": case_total,
                # 执行用例总数
                # "execute_case_total": execute_case_total,
                # 执行总次数
                # "execute_times_total": execute_times_total,
                # # 项目id
                # "project_id" : project_id,
                # # 子系统id
                # "subsys_id" : subsys_id,
                # # 测试单id
                # "test_sheet_id" : test_sheet_id,
                # # case创建人
                # "creator_name" : creator_name,
                # # 执行人
                # "executor_name" : executor_name,
                # # 执行结果
                # "execute_result" : execute_result,
                # # 执行起始日期
                # "date_execute_start" : date_execute_start,
                # # 执行截止日期
                # "date_execute_end" : date_execute_end,
                # # 用例类型
                # "case_type" : case_type,
                # case详情清单
                "case_detail_list": [{
                    # projectId
                    "project_id": each_case.get('project_id'),
                    # project名称
                    "project_name": each_case.get('pject_name'),
                    # subsysId
                    "subsys_id": each_case.get('subsys_id'),
                    # subsys名称
                    "subsys_name": each_case.get('subsys_name'),
                    # 测试单id
                    "task_id":each_case.get('task_id'),
                    # 测试单名称
                    "task_name": each_case.get('task_name'),
                    # 迭代id
                    "iteration_id":each_case.get('iteration_id'),
                    # 迭代名
                    "iteration_name": each_case.get('iteration_name'),
                    # 版本id
                    "version_id": each_case.get('version_id'),
                    # 版本名
                    "version_name": each_case.get('version_name'),
                    # caseId
                    "case_id": each_case.get('case_id'),
                    # case优先级
                    "case_priority": each_case.get('priority'),
                    # case标题
                    "case_title": each_case.get('title'),
                    # case类型
                    "case_type": case_type_constant_responding_English.get(each_case.get('case_type')),
                    # 指派给
                    "assigned_to": each_case.get('assigned_to'),
                    # 执行人
                    "executor": each_case.get('executor'),
                    # 执行时间
                    "execute_date": serialize_json_date_type(each_case.get('execute_date')),
                    # 创建人
                    "creator": each_case.get('creator'),
                    # 创建时间
                    "create_date": serialize_json_date_type(each_case.get('create_date')),
                    # 执行次数
                    "execute_times": each_case.get('execute_num'),
                    # 产生Bug数
                    "Bug_nums": each_case.get('bug_num'),
                    # 结果
                    "execute_result": each_case.get('last_run_result'),
                    #
                } for each_case in case_condition_search_result if each_case]
            }
        }
        # 返回数据结构构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')



async def caseCreateStatistics(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        print('-----------', params)
        # 项目id
        project_id = params.get('project_id', None)
        # 子系统id
        subsys_id = params.get('subsys_id', None)
        # 迭代id
        iteration_id = params.get('iteration_id', None)
        # 版本id
        version_id = params.get('version_id', None)
        # 模块id
        module_id = params.get('module_id', None)
        # 用例id
        case_id = params.get('case_id', None)
        # 用例类型
        case_type = params.get('case_type', None)
        case_type = case_type if type(case_type) is str else case_type[0]
        print('=============>', case_type)
        # 需求id
        story_id = params.get('story_id', None)
        # 用例来自于
        case_from = params.get('case_from', None)
        # case创建人
        creator_name = params.get('creator_name', None)
        # 创建起始日期
        date_create_start = params.get('date_create_start', None)
        # 创建截止日期
        date_create_end = params.get('date_create_end', None)
        # 页码
        pageNo = params.get('pageNo', 0)
        # 页码显示数量
        pageNum = params.get('pageNum', 25)

        # case 条件查询的数据结果
        case_condition_search_result = []
        # 按照数据类型分类
        case_extension_sql = construct_sql_case_create_statistics_detail(
                                        _project_id=project_id,
                                        _subsys_id=subsys_id,
                                        _iteration_id=iteration_id,
                                        _version_id=version_id,
                                        _module_id=module_id,
                                        _case_id=case_id,
                                        _case_type=case_type_constant_responding_Chinese.get(case_type),
                                        _story_id=story_id,
                                        _case_from=case_from,
                                        _creator_name=creator_name,
                                        _date_create_start=date_create_start,
                                        _date_create_end=date_create_end,
                                        _pageNum=pageNum,
                                        _pageNo=pageNo)
        case_extension_total_sql = construct_sql_case_create_statistics_all_sql_detail(
            _project_id=project_id,
            _subsys_id=subsys_id,
            _iteration_id=iteration_id,
            _version_id=version_id,
            _module_id=module_id,
            _case_id=case_id,
            _case_type=case_type_constant_responding_Chinese.get(case_type),
            _story_id=story_id,
            _case_from=case_from,
            _creator_name=creator_name,
            _date_create_start=date_create_start,
            _date_create_end=date_create_end
        )
        print('查询数据结果', case_extension_sql)
        print('统计结果', case_extension_total_sql)
        case_condition_search_result = cursor.search_alone(case_extension_sql)
        case_total = cursor.search_alone(case_extension_total_sql)[0].get('total')
        print('输出3个数据作为样式', case_condition_search_result[:3])

        json_returns = {
            'data': {
                # case总数
                "case_total": case_total,
                # 用例类型
                "case_type": case_type,
                # case详情清单
                "case_detail_list": [{
                    # projectId
                    "project_id": each_case.get('project_id'),
                    # project名称
                    "project_name": each_case.get('pject_name'),
                    # subsysId
                    "subsys_id": each_case.get('subsys_id'),
                    # subsys名称
                    "subsys_name": each_case.get('subsys_name'),
                    # 迭代id
                    "iteration_id": each_case.get('iteration_id'),
                    # 迭代名
                    "iteration_name": each_case.get('iteration_name'),
                    # 版本id
                    "version_id": each_case.get('version_id'),
                    # 版本名
                    "version_name": each_case.get('version_name'),
                    # 模块id
                    "module_id": each_case.get('module_id'),
                    # 模块id
                    "module_name": each_case.get('module_name'),
                    # 需求id
                    "story_id": each_case.get('story_id'),
                    # 需求名称
                    "story_name": each_case.get('story_title'),
                    # caseId
                    "case_id": each_case.get('case_id'),
                    # case优先级
                    "case_priority": each_case.get('priority'),
                    # case标题
                    "case_title": each_case.get('title'),
                    # case类型
                    "case_type": case_type_constant_responding_English.get(each_case.get('case_type')),
                    # 创建人
                    "creator": each_case.get('creator'),
                    # 用例来自
                    "case_from": each_case.get('case_from'),
                    # 创建时间
                    "create_date": serialize_json_date_type(each_case.get('create_date')),
                } for each_case in case_condition_search_result if each_case]
            }
        }
        # 返回数据结构构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')



async def executeTimeSlotShowByProject(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        # print('-----------', params)
        # 执行的创建起始时间
        date_execute_start = params.get('date_execute_start', None)
        # 执行的创建行截止日期
        date_execute_end = params.get('date_execute_end', None)
        # 用例类型
        case_type = params.get('case_type', None)
        # 执行人
        executor_name = params.get('executor', None)


        # case_execute_search_sql_all = construct_sql_case_execute_data_distribute_by_time_slot_with_all(
        #     _date_execute_start=date_execute_start,
        #     _date_execute_end=date_execute_end,
        #     _executor_name=executor_name,
        #     _case_type=case_type_constant_responding_Chinese.get(case_type)
        # )
        case_execute_search_sql_each = construct_sql_case_execute_data_distribute_by_time_project_slot_with_each(
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end,
            _executor_name=tuple(executor_name),
            _case_type=tuple(case_type_constant_responding_Chinese.get(each) for each in case_type)
        )
        print(case_execute_search_sql_each)
        # case_execute_search_result = cursor.search_alone(case_execute_search_sql)
        # case_execute_search_result_all = cursor.search_alone(case_execute_search_sql_all)
        case_execute_search_result_each = cursor.search_alone(case_execute_search_sql_each)
        all_case_execute_output_result = case_execute_search_result_each

        json_returns = {
            'data': {
                "date_execute_start": date_execute_start,  # Bug创建起始时间
                "date_execute_end": date_execute_end,  # Bug创建行截止日期
                "execute_detail_list": [{
                    # 执行人
                    "executor": each_execute.get('executor'),
                    # 项目id
                    "project_id": each_execute.get('project_id'),
                    # 项目名称
                    "project_name": each_execute.get('project_name'),
                    # 数据分布列表
                    "data_distribute": [
                        {
                            # 时间段
                            "timeSlot": f"{time_slot}:{'0' if time_slot < 13 else '3'}0 ~ {time_slot + 1 if time_slot < 22 else 8}:{'0' if time_slot + 1 < 13 or time_slot == 22 else '3'}0",
                            # 对应的执行数量
                            "num": each_execute.get("+" + str(time_slot))} for time_slot in range(8, 23)
                    ],
                    # 执行总数
                    "total": each_execute.get('total')
                } for each_execute in all_case_execute_output_result if each_execute]
            }
        }
        # 返回数据结构的构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executeDateShowByProject(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        print('-----------', params)
        # 执行的创建起始时间
        date_execute_start = params.get('date_execute_start', None)
        # 执行的创建行截止日期
        date_execute_end = params.get('date_execute_end', None)
        # 用例类型
        case_type = params.get('case_type', None)
        # 执行人
        executor_name = params.get('executor', None)


        # case_execute_search_sql_all = construct_sql_case_execute_data_distribute_by_date_with_all(
        #     _date_execute_start=date_execute_start,
        #     _date_execute_end=date_execute_end,
        #     _executor_name=executor_name,
        #     _case_type=case_type_constant_responding_Chinese.get(case_type)
        # )
        case_execute_search_sql_each = construct_sql_case_execute_data_distribute_by_date_project_with_each(
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end,
            _executor_name=executor_name,
            _case_type=tuple(case_type_constant_responding_Chinese.get(each) for each in case_type)
        )
        print(case_execute_search_sql_each)
        # case_execute_search_result = cursor.search_alone(case_execute_search_sql)
        # case_execute_search_result_all = cursor.search_alone(case_execute_search_sql_all)
        case_execute_search_result_each = cursor.search_alone(case_execute_search_sql_each)
        all_case_execute_output_result = case_execute_search_result_each


        json_returns = {
            'data': {
                "date_execute_start": date_execute_start, # Bug创建起始时间
                "date_execute_end": date_execute_end, # Bug创建行截止日期
                "execute_detail_list": [{
                    # 执行人
                    "executor": each_execute.get('executor'),
                    # 项目id
                    "project_id": each_execute.get('project_id'),
                    # 项目名称
                    "project_name": each_execute.get('project_name'),
                    # 数据分布列表
                    "data_distribute": [
                        {
                            # 日期
                            "date": str(day_index),
                            # 对应的执行数量
                            "num": each_execute.get(str(day_index))} for day_index in range(1, 32)
                    ],
                    # 执行总数
                    "total": each_execute.get('total')
                } for each_execute in all_case_execute_output_result if each_execute]
            }
        }
        # 返回数据结构的构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def createDateShowByDateProject(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        # print('-----------', params)
        # 执行的创建起始时间
        date_create_start = params.get('date_create_start', None)
        # 执行的创建行截止日期
        date_create_end = params.get('date_create_end', None)
        # 创建人
        creator_name = params.get('creator', None)

        # case_create_search_sql = construct_sql_case_create_data_distribute_by_date_with_all(
        #     _date_create_start=date_create_start,
        #     _date_create_end=date_create_end,
        #     _creator_name=creator_name
        # )
        case_create_search_sql = construct_sql_case_create_data_distribute_by_date_project_with_each(
            _date_create_start=date_create_start,
            _date_create_end=date_create_end,
            _creator_name=tuple(creator_name)
        )
        print(case_create_search_sql)
        case_create_search_result = cursor.search_alone(case_create_search_sql)



        json_returns = {
            'data': {
                "date_create_start": date_create_start, # Bug创建起始时间
                "date_create_end": date_create_end, # Bug创建行截止日期
                    # 数据分布列表
                    "data_distribute": [
                        {
                            # case创建人
                            "creator": each_create.get('creator') if each_create.get('creator') is not None else "-不详-",
                            # 项目id
                            "project_id": each_create.get('project_id'),
                            # 项目名称
                            "project_name": each_create.get('project_name'),
                            # 数据分布列表
                            "case_create_data_by_date": [
                                {"date": str(date),
                                 "num": each_create.get(str(date))
                                 } for date in range(1, 32)
                            ],
                            # 用例创建总数
                            "total": each_create.get('total')
                        } for each_create in case_create_search_result if each_create]
                    }
                }
        # 返回数据结构的构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def testSheetList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        project_id = int(request.GET.get('project_id', 0))
        subsys_id = int(request.GET.get('subsys_id', 0))
        # 对传参做判断与分类
        if project_id or subsys_id:
            test_sheet_sql = construct_sql_case_test_sheet_by_project_search_sql(
                _project_id=project_id,
                _subsys_id=subsys_id
            )
        elif project_id == 0 or not project_id:
            test_sheet_sql = construct_sql_case_test_sheet_by_project_search_sql()
        print(f'【case】test_sheet_sql: {test_sheet_sql}')
        # 查询测试单
        subsys_list = cursor.search_alone(test_sheet_sql)
        # print(f'【case】subsys 列表的查询结果: {subsys_list}')
        json_returns = {
            'data': {
                "subsys_names": subsys_list,
                'total': len(subsys_list)
            }
        }
        response = HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def subsysList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        project_id = int(request.GET.get('project_id', 0))
        # 对传参做判断与分类
        if project_id:
            subsys_sql = construct_sql_case_subsys_by_project_search_sql(
                _project_id=project_id
            )
        elif project_id == 0 or not project_id:
            subsys_sql = construct_sql_case_subsys_by_project_search_sql()
        print(f'【case】subsys_sql: {subsys_sql}')
        # 查询测试人员名单
        subsys_list = cursor.search_alone(subsys_sql)
        # print(f'【case】subsys 列表的查询结果: {subsys_list}')
        json_returns = {
            'data': {
                "subsys_names": subsys_list,
                'total': len(subsys_list)
            }
        }
        response = HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def iterationList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        subsys_id = int(request.GET.get('subsys_id', 0))
        # 对传参做判断与分类
        if subsys_id:
            iteration_sql = construct_sql_case_iteraion_by_subsys_search_sql(
                _subsys_id=subsys_id
            )
        elif subsys_id == 0 or not subsys_id:
            iteration_sql = construct_sql_case_iteraion_by_subsys_search_sql()
        print(f'【case】iteration_sql: {iteration_sql}')
        # 查询测试人员名单
        iteration_list = cursor.search_alone(iteration_sql)
        # print(f'【case】iteration 列表的查询结果: {iteration_list}')
        json_returns = {
            'data': {
                "iteration_names": iteration_list,
                'total': len(iteration_list)
            }
        }
        response = HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def versionList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        iteration_id = int(request.GET.get('iteration_id', 0))
        # 对传参做判断与分类
        if iteration_id:
            version_sql = construct_sql_case_iteraion_by_iteraion_search_sql(
                _iteration_id=iteration_id
            )
        elif iteration_id == 0 or not iteration_id:
            version_sql = construct_sql_case_iteraion_by_iteraion_search_sql()
        print(f'【case】version_sql: {version_sql}')
        # 查询测试人员名单
        version_list = cursor.search_alone(version_sql)
        # print(f'【case】version 列表的查询结果: {version_list}')
        json_returns = {
            'data': {
                "version_names": version_list,
                'total': len(version_list)
            }
        }
        response = HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executorList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        # 查询测试人员名单
        executor_name_list = cursor.search_alone(testcase_executor_name_list_sql)
        print(executor_name_list)

        json_returns = {
            'data': {
                "executor_names": executor_name_list,
                'total': len(executor_name_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executeDateShow(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        print('-----------', params)
        # 执行的创建起始时间
        date_execute_start = params.get('date_execute_start', None)
        # 执行的创建行截止日期
        date_execute_end = params.get('date_execute_end', None)
        # 用例类型
        case_type = params.get('case_type', None)
        # 执行人
        executor_name = params.get('executor_name', None)


        # case_execute_search_sql = construct_sql_case_execute_data_distribute_by_date(
        #     _date_execute_start=date_execute_start,
        #     _date_execute_end=date_execute_end
        # )
        case_execute_search_sql_all = construct_sql_testcase_execute_data_distribute_by_date_with_all(
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end,
            _executor_name=executor_name,
            _case_type=case_type_constant_responding_Chinese.get(case_type)
        )
        case_execute_search_sql_each = construct_sql_testcase_execute_data_distribute_by_date_with_each(
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end,
            _executor_name=executor_name,
            _case_type=case_type_constant_responding_Chinese.get(case_type)
        )
        print(case_execute_search_sql_each)
        # case_execute_search_result = cursor.search_alone(case_execute_search_sql)
        case_execute_search_result_all = cursor.search_alone(case_execute_search_sql_all)
        case_execute_search_result_each = cursor.search_alone(case_execute_search_sql_each)
        all_case_execute_output_result = list(case_execute_search_result_all) + list(case_execute_search_result_each)


        json_returns = {
            'data': {
                "date_execute_start": date_execute_start, # Bug创建起始时间
                "date_execute_end": date_execute_end, # Bug创建行截止日期
                "execute_detail_list": [{
                    # 执行人
                    "executor": each_execute.get('executor'),
                    # 数据分布列表
                    "data_distribute": [
                        {
                            # 日期
                            "date": str(day_index),
                            # 对应的执行数量
                            "num": each_execute.get(str(day_index))} for day_index in range(1, 32)
                    ],
                    # 执行总数
                    "total": each_execute.get('total')
                } for each_execute in all_case_execute_output_result if each_execute]
            }
        }
        # 返回数据结构的构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executeTimeSlotShow(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        # print('-----------', params)
        # 执行的创建起始时间
        date_execute_start = params.get('date_execute_start', None)
        # 执行的创建行截止日期
        date_execute_end = params.get('date_execute_end', None)
        # 用例类型
        case_type = params.get('case_type', None)
        # 执行人
        executor_name = params.get('executor_name', None)

        # case_execute_search_sql = construct_sql_case_execute_data_distribute_by_time_slot(
        #     _date_execute_start=date_execute_start,
        #     _date_execute_end=date_execute_end
        # )
        case_execute_search_sql_all = construct_sql_testcase_execute_data_distribute_by_time_slot_with_all(
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end,
            _executor_name=executor_name,
            _case_type=case_type_constant_responding_Chinese.get(case_type)
        )
        case_execute_search_sql_each = construct_sql_testcase_execute_data_distribute_by_time_slot_with_each(
            _date_execute_start=date_execute_start,
            _date_execute_end=date_execute_end,
            _executor_name=executor_name,
            _case_type=case_type_constant_responding_Chinese.get(case_type)
        )
        print(case_execute_search_sql_each)
        # case_execute_search_result = cursor.search_alone(case_execute_search_sql)
        case_execute_search_result_all = cursor.search_alone(case_execute_search_sql_all)
        case_execute_search_result_each = cursor.search_alone(case_execute_search_sql_each)
        all_case_execute_output_result = list(case_execute_search_result_all) + list(case_execute_search_result_each)

        json_returns = {
            'data': {
                "date_execute_start": date_execute_start,  # Bug创建起始时间
                "date_execute_end": date_execute_end,  # Bug创建行截止日期
                "execute_detail_list": [{
                    # 执行人
                    "executor": each_execute.get('executor'),
                    # 数据分布列表
                    "data_distribute": [
                        {
                            # 时间段
                            "timeSlot": f"{time_slot}:{'0' if time_slot < 13 else '3'}0 ~ {time_slot + 1 if time_slot < 22 else 8}:{'0' if time_slot + 1 < 13 or time_slot == 22 else '3'}0",
                            # 对应的执行数量
                            "num": each_execute.get("+" + str(time_slot))} for time_slot in range(8, 23)
                    ],
                    # 执行总数
                    "total": each_execute.get('total')
                } for each_execute in all_case_execute_output_result if each_execute]
            }
        }
        # 返回数据结构的构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def createDateShowByDate(request):
    if request.method == 'POST':
        params_body: json = request.body
        params: dict = json.loads(params_body.decode())
        # print('-----------', params)
        # 执行的创建起始时间
        date_create_start = params.get('date_create_start', None)
        # 执行的创建行截止日期
        date_create_end = params.get('date_create_end', None)
        # 创建人
        creator_name = params.get('creator_name', None)
        # case_create_search_sql = construct_sql_case_create_data_distribute_by_date(
        #     _date_create_start=date_create_start,
        #     _date_create_end=date_create_end
        # )
        case_create_search_sql = construct_sql_testcase_create_data_distribute_by_date_with_all(
            _date_create_start=date_create_start,
            _date_create_end=date_create_end,
            _creator_name=creator_name
        )
        case_create_search_sql = construct_sql_testcase_create_data_distribute_by_date_with_each(
            _date_create_start=date_create_start,
            _date_create_end=date_create_end,
            _creator_name=creator_name
        )
        print(case_create_search_sql)
        case_create_search_result = cursor.search_alone(case_create_search_sql)


        json_returns = {
            'data': {
                "date_create_start": date_create_start, # Bug创建起始时间
                "date_create_end": date_create_end, # Bug创建行截止日期
                    # 数据分布列表
                    "data_distribute": [
                        {
                            # case创建人
                            "creator": each_create.get('creator') if each_create.get('creator') is not None else "-不详-",
                            # 数据分布列表
                            "case_create_data_by_date": [
                                {"date": str(date),
                                 "num": each_create.get(str(date))
                                 } for date in range(1, 32)
                            ],
                            # 用例创建总数
                            "total": each_create.get('total')
                        } for each_create in case_create_search_result if each_create]
                    }
                }
        # 返回数据结构的构造
        return HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def executeRestultList(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        print(case_execute_result_constant_list)

        json_returns = {
            'data': {
                "executor_names": case_execute_result_constant_list,
                'total': len(case_execute_result_constant_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')


async def caseStatuslist(request):
    if request.method == 'GET':
        # 获取get请求带的参数
        # date_range = int(request.GET.get('date_range', 7))
        print(case_status_constant_list)

        json_returns = {
            'data': {
                "executor_names": case_status_constant_list,
                'total': len(case_status_constant_list)
            }
        }
        response =  HttpResponse(serialize_json_dumps(json_returns), content_type="application/json")
        return response
    else:
        return HttpResponseBadRequest(content=b'request is a bad request')